﻿Imports System.Data.SqlClient
Imports RunEnv
Public Class CTableInfo
    Implements IEnumerable
    Public Property TableName As String
    Public Property IDFields As String()
    Public Property IsAutoID As Boolean
    Public Property IsNeedAudit As Boolean
    Public Property Fields As New Collection
    Public Property ManagerTitle As String

    Private dlgItem As dlgItem

    Public Sub New(ByRef managerTitle As String)
        Dim sql As String
        Dim idListStr As String
        Dim dt As DataTable
        Dim field As CField
        Try
            Me.ManagerTitle = managerTitle
            sql = "Select *" _
                & " From BSysBaseTable " _
                & " Where ManagerTitle ='" & managerTitle & "'"
            dt = gcnnDBMain.GetDataTable(sql)
            If dt.Rows.Count <> 1 Then Throw New Exception(managerTitle & "表信息错误!")
            TableName = dt.Rows(0).Item("TableName")
            idListStr = dt.Rows(0).Item("IDFields")
            If Len(idListStr) = 0 Then
                IDFields = Nothing
            Else
                IDFields = Split(idListStr, , , CompareMethod.Text)
            End If

            IsAutoID = IIf(IsDBNull(dt.Rows(0).Item("IsAutoID")), False, dt.Rows(0).Item("IsAutoID"))
            IsNeedAudit = IIf(IsDBNull(dt.Rows(0).Item("IsNeedAudit")), False, dt.Rows(0).Item("IsNeedAudit"))

            sql = "Select * " _
                & " From BSysBaseField " _
                & " Where ManagerTitle ='" & managerTitle & "'" _
                & " Order By FieldOrder"
            dt = gcnnDBMain.GetDataTable(sql)
            If dt.Rows.Count < 1 Then Throw New Exception(managerTitle & "字段错误！")
            For i As Integer = 0 To dt.Rows.Count - 1
                field = New CField(dt.Rows(i), Me)
                Fields.Add(field, field.FieldName)
            Next

            For Each field1 As CField In Fields
                If field1.FieldMode = CField.FieldRelation.RelationDependent Then
                    CType(Fields(field1.LocalFieldKey), CField).RegisterObserver(field1)
                End If
            Next
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

    Public Function GetEnumerator() As IEnumerator Implements IEnumerable.GetEnumerator
        Return Fields.GetEnumerator
    End Function

    Public Sub SetDlgItem(ByRef dlg As dlgItem)
        Me.dlgItem = dlg
    End Sub

    Public Sub SetValue(ByVal fieldName As String, ByVal value As Object)
        Me.dlgItem.SetValue(fieldName, value)
    End Sub

    Public Function GetValue(ByVal fieldName As String) As String
        Return Me.dlgItem.GetValue(fieldName)
    End Function

    Public Function GetSelectCommand() As SqlCommand
        Dim i As Integer
        Dim sqlHead As String
        Dim sqlTail As String
        Dim sqlWhere As String
        Dim selectCommand As New SqlCommand
        Dim tableNames As New Dictionary(Of String, String)

        selectCommand.Connection = gcnnDBMain.sqlConnection

        sqlHead = ""
        sqlTail = " From " & TableName

        For Each field As CField In Fields
            If field.FieldMode = CField.FieldRelation.RelationDependent Then
                If Len(sqlHead) = 0 Then
                    sqlHead = field.SourceTable & "." & field.SourceFieldValue & " " & " AS " & field.FieldName & " "
                Else
                    sqlHead = sqlHead & " , " _
                        & field.SourceTable & "." & field.SourceFieldValue & " " & " AS " & field.FieldName & " "
                End If
                If Not tableNames.ContainsKey(field.SourceTable) Then
                    tableNames.Add(field.SourceTable, field.SourceTable)
                    sqlTail = sqlTail & " Left Join " & field.SourceTable _
                        & " ON " & TableName & "." & field.LocalFieldKey & "=" & field.SourceTable & "." & field.SourceFieldKey
                End If
            Else
                If Len(sqlHead) = 0 Then
                    sqlHead = TableName & "." & field.FieldName
                Else
                    sqlHead = sqlHead & " , " _
                            & TableName & "." & field.FieldName
                End If
            End If
        Next

        sqlWhere = ""
        For i = LBound(IDFields) To UBound(IDFields)
            If Len(sqlWhere) = 0 Then
                sqlWhere = TableName + "." + IDFields(i) + " = @" + IDFields(i)
            Else
                sqlWhere = sqlWhere + " And " + TableName + "." + IDFields(i) + " = @" + IDFields(i)
            End If
            selectCommand.Parameters.Add("@" + IDFields(i), _
                            CDBConnection.GetSqlDbType(CType(Fields(IDFields(i)), CField).DataType), _
                            CType(Fields(IDFields(i)), CField).DataSize)
        Next
        sqlWhere = " Where " + sqlWhere
        selectCommand.CommandText = "Select " + sqlHead + sqlTail + sqlWhere
        Return selectCommand
    End Function

    Public Function GetUpdateCommand() As SqlCommand
        Dim sqlHead As String
        Dim sqlWhere As String
        Dim updateCommand As New SqlCommand
        Dim field As CField
        updateCommand.Connection = gcnnDBMain.sqlConnection
        sqlHead = ""
        For Each field In Fields
            If field.IsDBField Then
                If Len(sqlHead) = 0 Then
                    sqlHead = field.FieldName + "= @" + field.FieldName
                Else
                    sqlHead = sqlHead + "," + field.FieldName + "= @" + field.FieldName
                End If
                updateCommand.Parameters.Add("@" + field.FieldName, _
                                             CDBConnection.GetSqlDbType(field.DataType), field.DataSize, field.FieldName)
            End If
        Next

        sqlWhere = ""
        For i = LBound(IDFields) To UBound(IDFields)
            If Len(sqlWhere) = 0 Then
                sqlWhere = IDFields(i) + " = @Old" + IDFields(i)
            Else
                sqlWhere = sqlWhere + " And " + IDFields(i) + " = @Old" + IDFields(i)
            End If
            Dim parameter As SqlParameter = updateCommand.Parameters.Add("@Old" + IDFields(i), _
                          CDBConnection.GetSqlDbType(CType(Fields(IDFields(i)), CField).DataType), _
                          CType(Fields(IDFields(i)), CField).DataSize, IDFields(i))
            parameter.SourceVersion = DataRowVersion.Original
        Next
        sqlWhere = " Where " + sqlWhere
        updateCommand.CommandText = "Update " + TableName + " Set " + sqlHead + sqlWhere
        Return updateCommand
    End Function

    Public Function GetInsertCommand() As SqlCommand
        Dim sqlHead As String
        Dim sqlTail As String
        Dim insertCommand As New SqlCommand
        Dim field As CField
        insertCommand.Connection = gcnnDBMain.sqlConnection
        sqlHead = ""
        sqlTail = ""
        For Each field In Fields
            If field.IsDBField Then
                If Len(sqlHead) = 0 Then
                    sqlHead = field.FieldName
                    sqlTail = "@" + field.FieldName
                Else
                    sqlHead = sqlHead + ", " + field.FieldName
                    sqlTail = sqlTail + ", " + "@" + field.FieldName
                End If
                insertCommand.Parameters.Add("@" + field.FieldName, _
                            CDBConnection.GetSqlDbType(field.DataType), field.DataSize, field.FieldName)
            End If
        Next
        insertCommand.CommandText = "Insert Into " + TableName _
                + " (" + sqlHead + ") " _
                + " Values (" + sqlTail + ")"
        Return insertCommand
    End Function

End Class
